package course.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

<<<<<<< HEAD
import course.controller.CourseControllerListener;
import course.dao.ITeacherDao;
import course.db.DbConnection;
import course.po.Course;
import course.po.Student;
import course.po.Teacher;
import course.view.StudentFrame;
import course.view.TeacherFrame;

/**
 * @author yangliping
 *
 */
public class TeacherDaoImpl implements ITeacherDao {
	/**
	 *
	 * 新增教师信息
	 *
	 */
	@Override
	public void save(Teacher teacher) {
		Connection conn = DbConnection.getConnection();
		Course course = teacher.getCourse();
		int teaId = teacher.getTeaId();
		String teaNo = teacher.getTeaNo();
		String teaName = teacher.getTeaName();
		String teaSex = teacher.getTeaSex();
		Date teaAge = teacher.getTeaAge();
		int couId = teacher.getCourse().getCouId();
		String sql = "INSERT INTO teacher VALUES(?,?,?,?,?,?)";
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, teaId);
			ps.setString(2, teaNo);
			ps.setString(3, teaName);
			ps.setString(4, teaSex);
			ps.setDate(5, (java.sql.Date) teaAge);
			ps.setInt(6, couId);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnection.close(null, ps);
		}
	}

	/**
	 *
	 * 更新教师信息
	 *
	 */
	@Override
	public void update(Teacher teacher) {
		Connection conn = DbConnection.getConnection();
		Course course = teacher.getCourse();
		int teaId = teacher.getTeaId();
		String teaNo = teacher.getTeaNo();
		String teaName = teacher.getTeaName();
		String teaSex = teacher.getTeaSex();
		Date teaAge = teacher.getTeaAge();
		int couId = teacher.getCourse().getCouId();
		String couName = teacher.getCourse().getCouName();
		String sql = "UPDATE teacher,course SET teacher_no=?,teacher_name=?,teacher_sex=?,teacher_age=?"
				+ ",teacher_course_id=? WHERE course.course_id = teacher.teacher_course_id AND teacher_id=?";
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, teaNo);
			ps.setString(2, teaName);
			ps.setString(3, teaSex);
			ps.setDate(4, (java.sql.Date) teaAge);
			ps.setInt(5, couId);
			ps.setInt(6, teaId);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnection.close(null, ps);
		}

	}

	/**
	 *
	 * 删除教师信息
	 */
	@Override
	public void delete(int id) {
		Connection conn = DbConnection.getConnection();
		String sql = "DELETE FROM teacher WHERE teacher_id=?";
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnection.close(null, ps);
		}

	}

	/**
	 * 根据教师名查询教师的信息
	 *
	 */
	@Override
	public Teacher getOne(String name) {
		Connection conn = DbConnection.getConnection();
		List<Teacher> teacherList = new ArrayList<Teacher>();
		String sql = "SELECT teacher_id,teacher_no,teacher_name,teacher_sex,teacher_age"
				+ ",teacher_course_id ,course_name FROM teacher,course WHERE course.course_id = teacher.teacher_course_id AND teacher_name=?";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, name);
			rs = ps.executeQuery();
			while (rs.next()) {
				int teaId = rs.getInt(1);
				String teaNo = rs.getString(2);
				String teaName = rs.getString(3);
				String teaSex = rs.getString(4);
				Date teaAge = rs.getDate(5);
				int couId = rs.getInt(6);
				String couName = rs.getString(7);
				Course course = new Course(couId, couName);
				Teacher teacher = new Teacher(teaId, teaNo, teaName, teaSex, teaAge, course);
				teacherList.add(teacher);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnection.close(rs, ps);
		}
		return teacherList.get(0);
	}

	/**
	 * 根据编号查询教师的信息 teacher_id为主键
	 *
	 */
	@Override
	public Teacher get(int id) {
		Connection conn = DbConnection.getConnection();
		List<Teacher> teacherList = new ArrayList<Teacher>();
		String sql = "SELECT teacher_id,teacher_no,teacher_name,teacher_sex,teacher_age"
				+ ",teacher_course_id ,course_name FROM teacher,course WHERE course.course_id = teacher.teacher_course_id AND teacher_id=?";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs = ps.executeQuery();
			while (rs.next()) {
				int teaId = rs.getInt(1);
				String teaNo = rs.getString(2);
				String teaName = rs.getString(3);
				String teaSex = rs.getString(4);
				Date teaAge = rs.getDate(5);
				int couId = rs.getInt(6);
				String couName = rs.getString(7);
				Course course = new Course(couId, couName);
				Teacher teacher = new Teacher(teaId, teaNo, teaName, teaSex, teaAge, course);
				teacherList.add(teacher);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnection.close(rs, ps);
		}
		return teacherList.get(0);
	}

	// 得到教师界面个人信息
	@Override
	public Teacher getTeacher(String teaNum) {
		Connection conn = DbConnection.getConnection();
		List<Teacher> teacherList = new ArrayList<Teacher>();
		String sql = "SELECT teacher_no,teacher_name,teacher_sex,teacher_age"
				+ ",teacher_course_id ,course_name FROM teacher,course WHERE course.course_id = teacher.teacher_course_id AND teacher_no=?";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, teaNum);
			rs = ps.executeQuery();
			while (rs.next()) {
				String teaNo = rs.getString(1);
				String teaName = rs.getString(2);
				String teaSex = rs.getString(3);
				Date teaAge = rs.getDate(4);
				int couId = rs.getInt(5);
				String couName = rs.getString(6);
				Course course = new Course(couId,couName);
				System.out.println(course);
				Teacher teacher = new Teacher(teaNo,teaName,teaSex,teaAge,course);
				teacherList.add(teacher);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnection.close(rs, ps);
		}
		return teacherList.get(0);
	}

	/**
	 *
	 * 查询所有教师的信息
	 */
	@Override
	public List<Teacher> findAll() {
		Connection conn = DbConnection.getConnection();
		List<Teacher> teacherList = new ArrayList<Teacher>();
		String sql = "SELECT teacher_id,teacher_no,teacher_name,teacher_sex,teacher_age"
				+ ",teacher_course_id ,course_name  FROM teacher,course WHERE course.course_id = teacher.teacher_course_id";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				int teaId = rs.getInt(1);
				String teaNo = rs.getString(2);
				String teaName = rs.getString(3);
				String teaSex = rs.getString(4);
				Date teaAge = rs.getDate(5);
				int couId = rs.getInt(6);
				String couName = rs.getString(7);
				Course course = new Course(couId, couName);
				Teacher teacher = new Teacher(teaId, teaNo, teaName, teaSex, teaAge, course);
				teacherList.add(teacher);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnection.close(rs, ps);
		}
		return teacherList;
	}

	/**
	 * 根据教师职工号查询教师所教课程的信息
	 *
	 */
	@Override
	public List<Course> getTeaCourse(String teaNo) {
		Connection conn = DbConnection.getConnection();
		List<Course> courseList = new ArrayList<Course>();
		String sql = "SELECT course_id,course_type,course_no,course_name,course_time_place,course_credit"
				+ ",course_teacher_id,teacher_name,course_max_number,course_select_number FROM course,teacher WHERE teacher.teacher_id=course.course_teacher_id AND teacher_no=?";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, teaNo);
			rs = ps.executeQuery();
			while (rs.next()) {
				int couId = rs.getInt(1);
				String couType = rs.getString(2);
				String couNo = rs.getString(3);
				String couName = rs.getString(4);
				String couDetail = rs.getString(5);
				int couCredit = rs.getInt(6);
				int teaId = rs.getInt(7);
				String teaName = rs.getString(8);
				int maxNumber = rs.getInt(9);
				int selectNumber = rs.getInt(10);
				Teacher teacher = new Teacher(teaId, teaName);
				Course course = new Course(couId, couType, couNo, couName, couDetail, couCredit, maxNumber,
						selectNumber, teacher);
				courseList.add(course);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnection.close(rs, ps);
		}
		return courseList;
	}

	/**
	 *
	 * 查询课程选修名单
	 */
	@Override
	public List<Student> findTeaStu(String teaNo) {
		Connection conn = DbConnection.getConnection();
		List<Student> studentList = new ArrayList<Student>();
		String sql = "SELECT student_id,student_no,student_name,student_sex,student_age"
				+ ",student_class_no,student_major,student_course_id,course_name,student_grade FROM student join course on student_course_id=course_id join teacher on teacher_course_id=course_id WHERE teacher_no=? ORDER BY student_class_no,student_grade";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, teaNo);
			rs = ps.executeQuery();
			while (rs.next()) {
				int stuId = rs.getInt(1);
				String stuNo = rs.getString(2);
				String stuName = rs.getString(3);
				String stuSex = rs.getString(4);
				Date stuAge = rs.getDate(5);
				int stuClassNo = rs.getInt(6);
				String stuMajor = rs.getString(7);
				int couId = rs.getInt(8);
				String couName = rs.getString(9);
				int stuGrade = rs.getInt(10);
				Course course = new Course(couId, couName);
				Student student = new Student(stuId, stuNo, stuName, stuSex, stuAge, stuMajor, stuClassNo, stuGrade,
						course);
				studentList.add(student);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnection.close(rs, ps);
		}
		return studentList;
	}
	/**
	 *
	 * 查询班级课程选修名单
	 */
	@Override
	public List<Student> findTeaClaStu(int clsNo) {
		Connection conn = DbConnection.getConnection();
		List<Student> studentList = new ArrayList<Student>();
		String sql = "SELECT student_id,student_no,student_name,student_sex,student_age"
				+ ",student_class_no,student_major,student_course_id,course_name,student_grade FROM student join course on student_course_id=course_id join teacher on teacher_course_id=course_id WHERE student_class_no=?";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, clsNo);
			rs = ps.executeQuery();
			while (rs.next()) {
				int stuId = rs.getInt(1);
				String stuNo = rs.getString(2);
				String stuName = rs.getString(3);
				String stuSex = rs.getString(4);
				Date stuAge = rs.getDate(5);
				int stuClassNo = rs.getInt(6);
				String stuMajor = rs.getString(7);
				int couId = rs.getInt(8);
				String couName = rs.getString(9);
				int stuGrade = rs.getInt(10);
				Course course = new Course(couId, couName);
				Student student = new Student(stuId, stuNo, stuName, stuSex, stuAge, stuMajor, stuClassNo, stuGrade,
						course);
				studentList.add(student);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnection.close(rs, ps);
		}
		return studentList;
	}
	/**
	 *
	 *
	 * 教师对自己所授课程的学生的成绩进行录入、修改
	 *
	 */
	@Override
	public void updateStuGrade(String teaNo, int stuGrade,String stuNo) {
		Connection conn = DbConnection.getConnection();
		String sql = "UPDATE student,course,teacher SET "
				+ "student_grade=? WHERE student.student_course_id=course.course_id=teacher_course_id AND teacher_no=? AND student_no=?";
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, stuGrade);
			ps.setString(2, teaNo);
			ps.setString(3, stuNo);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnection.close(null, ps);
		}

	}

	/**
	 * 根据学生学号查询学生成绩
	 *
	 */
	@Override
	public List<Student> getStuNoGrade(String stNo,String teaNo) {
		Connection conn = DbConnection.getConnection();
		List<Student> studentList = new ArrayList<Student>();
		String sql = "SELECT student_id,student_no,student_name"
				+ ",student_class_no,student_major,student_course_id,course_name,student_grade FROM student join course on student_course_id=course_id join teacher on teacher_course_id=course_id WHERE teacher_no=? AND student_no=?";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, teaNo);
			ps.setString(2, stNo);
			rs = ps.executeQuery();
			while (rs.next()) {
				int stuId = rs.getInt(1);
				String stuNo = rs.getString(2);
				String stuName = rs.getString(3);
				int stuClassNo = rs.getInt(4);
				String stuMajor = rs.getString(5);
				int couId = rs.getInt(6);
				String couName = rs.getString(7);
				int stuGrade = rs.getInt(8);
				Course course = new Course(couId, couName);
				Student student = new Student(stuId, stuNo, stuName, stuMajor, stuClassNo, stuGrade, course);
				studentList.add(student);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnection.close(rs, ps);
		}
		return studentList;
	}

	/**
	 * 根据学生课程名称查询学生成绩
	 *
	 */

	public List<Student> getCourseNoGrade(String stuCouName,String teaNo) {
		Connection conn = DbConnection.getConnection();
		List<Student> studentList = new ArrayList<Student>();
		String sql = "SELECT student_id,student_no,student_name"
				+ ",student_class_no,student_major,student_course_id,course_name,student_grade FROM student join course on student_course_id=course_id join teacher on teacher_course_id=course_id WHERE teacher_no=? AND course_name LIKE CONCAT('%',?,'%')";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, teaNo);
			ps.setString(2, stuCouName);
			rs = ps.executeQuery();
			while (rs.next()) {
				int stuId = rs.getInt(1);
				String stuNo = rs.getString(2);
				String stuName = rs.getString(3);
				int stuClassNo = rs.getInt(4);
				String stuMajor = rs.getString(5);
				int couId = rs.getInt(6);
				String couName = rs.getString(7);
				int stuGrade = rs.getInt(8);
				Course course = new Course(couId, couName);
				Student student = new Student(stuId, stuNo, stuName, stuMajor, stuClassNo, stuGrade, course);
				studentList.add(student);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnection.close(rs, ps);
		}
		return studentList;
	}

	/**
	 * 根据学生班级号查询学生成绩
	 *
	 */
	@Override
	public List<Student> getClassNoGrade(int classNo,String teaNo) {
		Connection conn = DbConnection.getConnection();
		List<Student> studentList = new ArrayList<Student>();
		String sql = "SELECT student_id,student_no,student_name"
				+ ",student_class_no,student_major,student_course_id,course_name,student_grade FROM student join course on student_course_id=course_id join teacher on teacher_course_id=course_id WHERE teacher_no=? AND student_class_no=?";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			
			ps.setString(1, teaNo);
			ps.setInt(2, classNo);
			rs = ps.executeQuery();
			while (rs.next()) {
				int stuId = rs.getInt(1);
				String stuNo = rs.getString(2);
				String stuName = rs.getString(3);
				int stuClassNo = rs.getInt(4);
				String stuMajor = rs.getString(5);
				int couId = rs.getInt(6);
				String couName = rs.getString(7);
				int stuGrade = rs.getInt(8);
				Course course = new Course(couId, couName);
				Student student = new Student(stuId, stuNo, stuName, stuMajor, stuClassNo, stuGrade, course);
				studentList.add(student);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnection.close(rs, ps);
		}
		return studentList;
	}
	//根据不同时段查找课程
	@Override
	public List<Course> getTimeCourse(String time,String teaN) {
		Connection conn = DbConnection.getConnection();
		List<Course> courseList = new ArrayList<Course>();
		String sql = "SELECT course_id,course_type,course_no,course_name,course_time_place,course_credit"
				+ ",course_teacher_id,teacher_name,course_max_number,course_select_number FROM course,teacher WHERE teacher.teacher_id=course.course_teacher_id AND teacher_no=? AND course_time_place LIKE CONCAT('%',?,'%')";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, teaN);
			ps.setString(2, time);
			rs = ps.executeQuery();
			while (rs.next()) {
				int couId = rs.getInt(1);
				String couType = rs.getString(2);
				String couNo = rs.getString(3);
				String couName = rs.getString(4);
				String couDetail = rs.getString(5);
				int couCredit = rs.getInt(6);
				int teaId = rs.getInt(7);
				String teaName = rs.getString(8);
				int maxNumber = rs.getInt(9);
				int selectNumber = rs.getInt(10);
				Teacher teacher = new Teacher(teaId, teaName);
				Course course = new Course(couId, couType, couNo, couName, couDetail, couCredit, maxNumber,
						selectNumber, teacher);
				courseList.add(course);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnection.close(rs, ps);
		}
		return courseList;
	}
	/**
	 * 根据学生专业名称查询学生成绩
	 *
	 */

	public List<Student> getCourseMajGrade(String major,String teaNo) {
		Connection conn = DbConnection.getConnection();
		List<Student> studentList = new ArrayList<Student>();
		String sql = "SELECT student_id,student_no,student_name"
				+ ",student_class_no,student_major,student_course_id,course_name,student_grade FROM student join course on student_course_id=course_id join teacher on teacher_course_id=course_id WHERE teacher_no=? AND student_major LIKE CONCAT('%',?,'%')";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, teaNo);
			ps.setString(2, major);
			rs = ps.executeQuery();
			while (rs.next()) {
				int stuId = rs.getInt(1);
				String stuNo = rs.getString(2);
				String stuName = rs.getString(3);
				int stuClassNo = rs.getInt(4);
				String stuMajor = rs.getString(5);
				int couId = rs.getInt(6);
				String couName = rs.getString(7);
				int stuGrade = rs.getInt(8);
				Course course = new Course(couId, couName);
				Student student = new Student(stuId, stuNo, stuName, stuMajor, stuClassNo, stuGrade, course);
				studentList.add(student);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnection.close(rs, ps);
		}
		return studentList;
	}
	// 查看同一门课程平均分
    @Override
 	public int getA() {
		Connection conn = DbConnection.getConnection();
		String sql ="SELECT AVG(student_grade) FROM student join course on student_course_id=course_id join teacher on teacher_course_id=course_id WHERE course_name LIKE CONCAT('%',?,'%') AND teacher_no=?";
		int grade = 0;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1,TeacherFrame.t2.getText());
			ps.setString(2,CourseControllerListener.nameInput);
			rs = ps.executeQuery();
			List<Integer> list = new ArrayList<Integer>();// 创建取结果的列表，之所以使用列表，不用数组，因为现在还不知道结果有多少，不能确定数组长度，所有先用list接收，然后转为数组
			while (rs.next()) {// 如果有数据，取第一列添加如list
				list.add(rs.getInt(1));
			}
			if (list != null && list.size() > 0) {// 如果list中存入了数据，转化为数组
				Integer[] arr = new Integer[list.size()];// 创建一个和list长度一样的数组
				for (int i = 0; i < list.size(); i++) {
					arr[i] = list.get(i);// 数组赋值了。
				}
				for (int i = 0; i < list.size(); i++) {
					grade = grade + arr[i];
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnection.close(rs, ps);
		}
		return grade;
	}
 // 查看同一门课程最高分
    @Override
 	public int getM() {
		Connection conn = DbConnection.getConnection();
		String sql ="SELECT MAX(student_grade) FROM student join course on student_course_id=course_id join teacher on teacher_course_id=course_id WHERE course_name LIKE CONCAT('%',?,'%') AND teacher_no=?";
		int grade = 0;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1,TeacherFrame.t2.getText());
			ps.setString(2,CourseControllerListener.nameInput);
			rs = ps.executeQuery();
			List<Integer> list = new ArrayList<Integer>();// 创建取结果的列表，之所以使用列表，不用数组，因为现在还不知道结果有多少，不能确定数组长度，所有先用list接收，然后转为数组
			while (rs.next()) {// 如果有数据，取第一列添加如list
				list.add(rs.getInt(1));
			}
			if (list != null && list.size() > 0) {// 如果list中存入了数据，转化为数组
				Integer[] arr = new Integer[list.size()];// 创建一个和list长度一样的数组
				for (int i = 0; i < list.size(); i++) {
					arr[i] = list.get(i);// 数组赋值了。
				}
				for (int i = 0; i < list.size(); i++) {
					grade = grade + arr[i];
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnection.close(rs, ps);
		}
		return grade;
	}
 // 查看同一门课程最低分
    @Override
 	public int getL() {
		Connection conn = DbConnection.getConnection();
		String sql ="SELECT MIN(student_grade) FROM student join course on student_course_id=course_id join teacher on teacher_course_id=course_id WHERE course_name LIKE CONCAT('%',?,'%') AND teacher_no=?";
		int grade = 0;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1,TeacherFrame.t2.getText());
			ps.setString(2,CourseControllerListener.nameInput);
			rs = ps.executeQuery();
			List<Integer> list = new ArrayList<Integer>();// 创建取结果的列表，之所以使用列表，不用数组，因为现在还不知道结果有多少，不能确定数组长度，所有先用list接收，然后转为数组
			while (rs.next()) {// 如果有数据，取第一列添加如list
				list.add(rs.getInt(1));
			}
			if (list != null && list.size() > 0) {// 如果list中存入了数据，转化为数组
				Integer[] arr = new Integer[list.size()];// 创建一个和list长度一样的数组
				for (int i = 0; i < list.size(); i++) {
					arr[i] = list.get(i);// 数组赋值了。
				}
				for (int i = 0; i < list.size(); i++) {
					grade = grade + arr[i];
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnection.close(rs, ps);
		}
		return grade;
	}
 // 查看同一门课程在0~59分区间的人数
    @Override
 	public int getN() {
		Connection conn = DbConnection.getConnection();
		String sql ="SELECT COUNT(*)  FROM student join course on student_course_id=course_id join teacher on teacher_course_id=course_id WHERE course_name LIKE CONCAT('%',?,'%')  AND student_grade BETWEEN 0 and 59";
		int grade = 0;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1,TeacherFrame.t2.getText());
			rs = ps.executeQuery();
			List<Integer> list = new ArrayList<Integer>();// 创建取结果的列表，之所以使用列表，不用数组，因为现在还不知道结果有多少，不能确定数组长度，所有先用list接收，然后转为数组
			while (rs.next()) {// 如果有数据，取第一列添加如list
				list.add(rs.getInt(1));
			}
			if (list != null && list.size() > 0) {// 如果list中存入了数据，转化为数组
				Integer[] arr = new Integer[list.size()];// 创建一个和list长度一样的数组
				for (int i = 0; i < list.size(); i++) {
					arr[i] = list.get(i);// 数组赋值了。
				}
				for (int i = 0; i < list.size(); i++) {
					grade = grade + arr[i];
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnection.close(rs, ps);
		}
		return grade;
	}
    // 查看同一门课程在60~69分区间的人数
    @Override
 	public int getO() {
		Connection conn = DbConnection.getConnection();
		String sql ="SELECT COUNT(*)  FROM student join course on student_course_id=course_id join teacher on teacher_course_id=course_id WHERE course_name LIKE CONCAT('%',?,'%')  AND student_grade BETWEEN 60 and 69";
		int grade = 0;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1,TeacherFrame.t2.getText());
			
			rs = ps.executeQuery();
			List<Integer> list = new ArrayList<Integer>();// 创建取结果的列表，之所以使用列表，不用数组，因为现在还不知道结果有多少，不能确定数组长度，所有先用list接收，然后转为数组
			while (rs.next()) {// 如果有数据，取第一列添加如list
				list.add(rs.getInt(1));
			}
			if (list != null && list.size() > 0) {// 如果list中存入了数据，转化为数组
				Integer[] arr = new Integer[list.size()];// 创建一个和list长度一样的数组
				for (int i = 0; i < list.size(); i++) {
					arr[i] = list.get(i);// 数组赋值了。
				}
				for (int i = 0; i < list.size(); i++) {
					grade = grade + arr[i];
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnection.close(rs, ps);
		}
		return grade;
	}
    // 查看同一门课程在70~85分区间的人数
    @Override
 	public int getP() {
		Connection conn = DbConnection.getConnection();
		String sql ="SELECT COUNT(*)  FROM student join course on student_course_id=course_id join teacher on teacher_course_id=course_id WHERE course_name LIKE CONCAT('%',?,'%')  AND student_grade BETWEEN 70 and 85";
		int grade = 0;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1,TeacherFrame.t2.getText());
			rs = ps.executeQuery();
			List<Integer> list = new ArrayList<Integer>();// 创建取结果的列表，之所以使用列表，不用数组，因为现在还不知道结果有多少，不能确定数组长度，所有先用list接收，然后转为数组
			while (rs.next()) {// 如果有数据，取第一列添加如list
				list.add(rs.getInt(1));
			}
			if (list != null && list.size() > 0) {// 如果list中存入了数据，转化为数组
				Integer[] arr = new Integer[list.size()];// 创建一个和list长度一样的数组
				for (int i = 0; i < list.size(); i++) {
					arr[i] = list.get(i);// 数组赋值了。
				}
				for (int i = 0; i < list.size(); i++) {
					grade = grade + arr[i];
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnection.close(rs, ps);
		}
		return grade;
	}
    // 查看同一门课程在86~100分区间的人数
    @Override
 	public int getQ() {
		Connection conn = DbConnection.getConnection();
		String sql ="SELECT COUNT(*)  FROM student join course on student_course_id=course_id join teacher on teacher_course_id=course_id WHERE course_name LIKE CONCAT('%',?,'%')  AND student_grade BETWEEN 86 and 100";
		int grade = 0;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1,TeacherFrame.t2.getText());
			rs = ps.executeQuery();
			List<Integer> list = new ArrayList<Integer>();// 创建取结果的列表，之所以使用列表，不用数组，因为现在还不知道结果有多少，不能确定数组长度，所有先用list接收，然后转为数组
			while (rs.next()) {// 如果有数据，取第一列添加如list
				list.add(rs.getInt(1));
			}
			if (list != null && list.size() > 0) {// 如果list中存入了数据，转化为数组
				Integer[] arr = new Integer[list.size()];// 创建一个和list长度一样的数组
				for (int i = 0; i < list.size(); i++) {
					arr[i] = list.get(i);// 数组赋值了。
				}
				for (int i = 0; i < list.size(); i++) {
					grade = grade + arr[i];
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnection.close(rs, ps);
		}
		return grade;
=======
import course.dao.ITeacherDao;
import course.db.DbConnection;
import course.po.Course;
import course.po.Student;
import course.po.Teacher;

/**
 * @author yangliping
 *
 */
/**
 * @author yangliping
 *
 */
public class TeacherDaoImpl implements ITeacherDao {
	/**
	 *
	 * 新增教师信息
	 *
	 */
	@Override
	public void save(Teacher teacher) {
		Connection conn = DbConnection.getConnection();
		Course course = teacher.getCourse();
		int teaId = teacher.getTeaId();
		String teaNo = teacher.getTeaNo();
		String teaName = teacher.getTeaName();
		String teaSex = teacher.getTeaSex();
		Date teaAge = teacher.getTeaAge();
		int couId = teacher.getCourse().getCouId();
		String sql = "INSERT INTO teacher VALUES(?,?,?,?,?,?)";
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, teaId);
			ps.setString(2, teaNo);
			ps.setString(3, teaName);
			ps.setString(4, teaSex);
			ps.setDate(5, (java.sql.Date) teaAge);
			ps.setInt(6, couId);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnection.close(null, ps);
		}
	}

	/**
	 *
	 * 更新教师信息
	 *
	 */
	@Override
	public void update(Teacher teacher) {
		Connection conn = DbConnection.getConnection();
		Course course = teacher.getCourse();
		int teaId = teacher.getTeaId();
		String teaNo = teacher.getTeaNo();
		String teaName = teacher.getTeaName();
		String teaSex = teacher.getTeaSex();
		Date teaAge = teacher.getTeaAge();
		int couId = teacher.getCourse().getCouId();
		String couName = teacher.getCourse().getCouName();
		String sql = "UPDATE teacher,course SET teacher_no=?,teacher_name=?,teacher_sex=?,teacher_age=?"
				+ ",teacher_course_id=? WHERE course.course_id = teacher.teacher_course_id AND teacher_id=?";
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, teaNo);
			ps.setString(2, teaName);
			ps.setString(3, teaSex);
			ps.setDate(4, (java.sql.Date) teaAge);
			ps.setInt(5, couId);
			ps.setInt(6, teaId);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnection.close(null, ps);
		}

	}

	/**
	 *
	 * 删除教师信息
	 */
	@Override
	public void delete(int id) {
		Connection conn = DbConnection.getConnection();
		String sql = "DELETE FROM teacher WHERE teacher_id=?";
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnection.close(null, ps);
		}

	}

	/**
	 * 根据教师名查询教师的信息
	 *
	 */
	@Override
	public Teacher getOne(String name) {
		Connection conn = DbConnection.getConnection();
		List<Teacher> teacherList = new ArrayList<Teacher>();
		String sql = "SELECT teacher_id,teacher_no,teacher_name,teacher_sex,teacher_age"
				+ ",teacher_course_id ,course_name FROM teacher,course WHERE course.course_id = teacher.teacher_course_id AND teacher_name=?";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, name);
			rs = ps.executeQuery();
			while (rs.next()) {
				int teaId = rs.getInt(1);
				String teaNo = rs.getString(2);
				String teaName = rs.getString(3);
				String teaSex = rs.getString(4);
				Date teaAge = rs.getDate(5);
				int couId = rs.getInt(6);
				String couName = rs.getString(7);
				Course course = new Course(couId, couName);
				Teacher teacher = new Teacher(teaId, teaNo, teaName, teaSex, teaAge, course);
				teacherList.add(teacher);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnection.close(rs, ps);
		}
		return teacherList.get(0);
	}

	/**
	 * 根据编号查询教师的信息 teacher_id为主键
	 *
	 */
	@Override
	public Teacher get(int id) {
		Connection conn = DbConnection.getConnection();
		List<Teacher> teacherList = new ArrayList<Teacher>();
		String sql = "SELECT teacher_id,teacher_no,teacher_name,teacher_sex,teacher_age"
				+ ",teacher_course_id ,course_name FROM teacher,course WHERE course.course_id = teacher.teacher_course_id AND teacher_id=?";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs = ps.executeQuery();
			while (rs.next()) {
				int teaId = rs.getInt(1);
				String teaNo = rs.getString(2);
				String teaName = rs.getString(3);
				String teaSex = rs.getString(4);
				Date teaAge = rs.getDate(5);
				int couId = rs.getInt(6);
				String couName = rs.getString(7);
				Course course = new Course(couId, couName);
				Teacher teacher = new Teacher(teaId, teaNo, teaName, teaSex, teaAge, course);
				teacherList.add(teacher);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnection.close(rs, ps);
		}
		return teacherList.get(0);
	}

	// 得到教师界面个人信息
	public Teacher getTeacher(String teaNum) {
		Connection conn = DbConnection.getConnection();
		List<Teacher> teacherList = new ArrayList<Teacher>();
		String sql = "SELECT teacher_no,teacher_name,teacher_sex,teacher_age"
				+ ",teacher_course_id ,course_name FROM teacher,course WHERE course.course_id = teacher.teacher_course_id AND teacher_no=?";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, teaNum);
			rs = ps.executeQuery();
			while (rs.next()) {
				String teaNo = rs.getString(1);
				String teaName = rs.getString(2);
				String teaSex = rs.getString(3);
				Date teaAge = rs.getDate(4);
				int couId = rs.getInt(5);
				String couName = rs.getString(6);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnection.close(rs, ps);
		}
		return teacherList.get(0);
	}

	/**
	 *
	 * 查询所有教师的信息
	 */
	@Override
	public List<Teacher> findAll() {
		Connection conn = DbConnection.getConnection();
		List<Teacher> teacherList = new ArrayList<Teacher>();
		String sql = "SELECT teacher_id,teacher_no,teacher_name,teacher_sex,teacher_age"
				+ ",teacher_course_id ,course_name  FROM teacher,course WHERE course.course_id = teacher.teacher_course_id";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				int teaId = rs.getInt(1);
				String teaNo = rs.getString(2);
				String teaName = rs.getString(3);
				String teaSex = rs.getString(4);
				Date teaAge = rs.getDate(5);
				int couId = rs.getInt(6);
				String couName = rs.getString(7);
				Course course = new Course(couId, couName);
				Teacher teacher = new Teacher(teaId, teaNo, teaName, teaSex, teaAge, course);
				teacherList.add(teacher);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnection.close(rs, ps);
		}
		return teacherList;
	}

	/**
	 * 根据教师职工号查询教师所教课程的信息
	 *
	 */
	@Override
	public List<Course> getTeaCourse(String teaNo) {
		Connection conn = DbConnection.getConnection();
		List<Course> courseList = new ArrayList<Course>();
		String sql = "SELECT course_id,course_type,course_no,course_name,course_time_place,course_credit"
				+ ",course_teacher_id,teacher_name,course_max_number,course_select_number FROM course,teacher WHERE teacher.teacher_id=course.course_teacher_id AND teacher_no=?";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, teaNo);
			rs = ps.executeQuery();
			while (rs.next()) {
				int couId = rs.getInt(1);
				String couType = rs.getString(2);
				String couNo = rs.getString(3);
				String couName = rs.getString(4);
				String couDetail = rs.getString(5);
				int couCredit = rs.getInt(6);
				int teaId = rs.getInt(7);
				String teaName = rs.getString(8);
				int maxNumber = rs.getInt(9);
				int selectNumber = rs.getInt(10);
				Teacher teacher = new Teacher(teaId, teaName);
				Course course = new Course(couId, couType, couNo, couName, couDetail, couCredit, maxNumber,
						selectNumber, teacher);
				courseList.add(course);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnection.close(rs, ps);
		}
		return courseList;
	}

	/**
	 *
	 * 查询课程选修名单
	 */
	@Override
	public List<Student> findTeaStu(String teaNo) {
		Connection conn = DbConnection.getConnection();
		List<Student> studentList = new ArrayList<Student>();
		String sql = "SELECT student_id,student_no,student_name,student_sex,student_age"
				+ ",student_class_no,student_major,student_course_id,course_name,student_grade FROM student join course on student_course_id=course_id join teacher on teacher_course_id=course_id WHERE teacher_no=?";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, teaNo);
			rs = ps.executeQuery();
			while (rs.next()) {
				int stuId = rs.getInt(1);
				String stuNo = rs.getString(2);
				String stuName = rs.getString(3);
				String stuSex = rs.getString(4);
				Date stuAge = rs.getDate(5);
				int stuClassNo = rs.getInt(6);
				String stuMajor = rs.getString(7);
				int couId = rs.getInt(8);
				String couName = rs.getString(9);
				int stuGrade = rs.getInt(10);
				Course course = new Course(couId, couName);
				Student student = new Student(stuId, stuNo, stuName, stuSex, stuAge, stuMajor, stuClassNo, stuGrade,
						course);
				studentList.add(student);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnection.close(rs, ps);
		}
		return studentList;
	}

	/**
	 *
	 *
	 * 教师对自己所授课程的学生的成绩进行录入、修改
	 *
	 */
	@Override
	public void updateStuGrade(String teaNo, int stuGrade) {
		Connection conn = DbConnection.getConnection();
		String sql = "UPDATE student,course,teacher SET "
				+ "student_grade=? WHERE student.student_course_id=course.course_id=teacher_course_id AND teacher_no=?";
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, stuGrade);
			ps.setString(2, teaNo);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnection.close(null, ps);
		}

	}

	/**
	 * 根据学生学号查询学生成绩
	 *
	 */
	@Override
	public List<Student> getStuNoGrade(String stNo) {
		Connection conn = DbConnection.getConnection();
		List<Student> studentList = new ArrayList<Student>();
		String sql = "SELECT student_id,student_no,student_name"
				+ ",student_class_no,student_major,student_course_id,course_name,student_grade FROM student,course WHERE student.student_course_id = course.course_id AND student_no=?";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, stNo);
			rs = ps.executeQuery();
			while (rs.next()) {
				int stuId = rs.getInt(1);
				String stuNo = rs.getString(2);
				String stuName = rs.getString(3);
				int stuClassNo = rs.getInt(4);
				String stuMajor = rs.getString(5);
				int couId = rs.getInt(6);
				String couName = rs.getString(7);
				int stuGrade = rs.getInt(8);
				Course course = new Course(couId, couName);
				Student student = new Student(stuId, stuNo, stuName, stuMajor, stuClassNo, stuGrade, course);
				studentList.add(student);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnection.close(rs, ps);
		}
		return studentList;
	}

	/**
	 * 根据学生课程名称查询学生成绩
	 *
	 */

	public List<Student> getCourseNoGrade(String stuCouName) {
		Connection conn = DbConnection.getConnection();
		List<Student> studentList = new ArrayList<Student>();
		String sql = "SELECT student_id,student_no,student_name"
				+ ",student_class_no,student_major,student_course_id,course_name,student_grade FROM student,course WHERE student.student_course_id = course.course_id AND course_name=?";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, stuCouName);
			rs = ps.executeQuery();
			while (rs.next()) {
				int stuId = rs.getInt(1);
				String stuNo = rs.getString(2);
				String stuName = rs.getString(3);
				int stuClassNo = rs.getInt(4);
				String stuMajor = rs.getString(5);
				int couId = rs.getInt(6);
				String couName = rs.getString(7);
				int stuGrade = rs.getInt(8);
				Course course = new Course(couId, couName);
				Student student = new Student(stuId, stuNo, stuName, stuMajor, stuClassNo, stuGrade, course);
				studentList.add(student);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnection.close(rs, ps);
		}
		return studentList;
	}

	/**
	 * 根据学生班级号查询学生成绩
	 *
	 */
	@Override
	public List<Student> getClassNoGrade(int classNo) {
		Connection conn = DbConnection.getConnection();
		List<Student> studentList = new ArrayList<Student>();
		String sql = "SELECT student_id,student_no,student_name"
				+ ",student_class_no,student_major,student_course_id,course_name,student_grade FROM student,course WHERE student.student_course_id = course.course_id AND student_class_no=?";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, classNo);
			rs = ps.executeQuery();
			while (rs.next()) {
				int stuId = rs.getInt(1);
				String stuNo = rs.getString(2);
				String stuName = rs.getString(3);
				int stuClassNo = rs.getInt(4);
				String stuMajor = rs.getString(5);
				int couId = rs.getInt(6);
				String couName = rs.getString(7);
				int stuGrade = rs.getInt(8);
				Course course = new Course(couId, couName);
				Student student = new Student(stuId, stuNo, stuName, stuMajor, stuClassNo, stuGrade, course);
				studentList.add(student);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnection.close(rs, ps);
		}
		return studentList;
>>>>>>> refs/remotes/origin/master
	}
}
